const xlsx = require('node-xlsx');
const path = require('path');
const fs = require('fs');
module.exports = async function (app, models, $, db, nm) {
    function getSchemas(tables){///获取数据字典
        const workSheetsFromFile = xlsx.parse(`./数据目录模板.xlsx`);
        let sheel0 = workSheetsFromFile[0].data;
        sheel0[0] = [
            '数据资源名称',
            '所属应用',
            '数据资源格式分类',
            '数据资源格式类型',
            '数据所属领域',
            '更新频率',
            '更新频率说明',
            '数据敏感级别',
            '数据资源摘要',
            '数据项-中文名称',
            '数据项-英文名称',
            '是否字典项',
            '字典表名称',
            '数据类型',
            '是否定长',
            '最小长度',
            '最大长度',
            '是否主键',
            '是否可为空',
            '共享属性',
            '共享条件/不予共享原因',
            '开放属性',
            '开放条件/不予开放原因',
            '字段描述',
            '默认值'
        ]
        let i = 0,j = 1
        for(let key in (tables || models)){
            let desc = models[key].nickName || ''
            if(models[key].desc) desc += ',' + models[key].desc
            createTable(key,models[key].schemas,desc,models[key].nickName || models[key].desc)
            // sheel1[j] = [j,key,desc,0,0,'否','','createTime','时间戳(毫秒)','']
            j ++
        }
        let excelPath = path.resolve('./数据字典导出结果.xlsx')
        fs.writeFile(excelPath, xlsx.build(workSheetsFromFile), err => {
            //console.log(err)
        })
        function createTable(name,models,desc,tableNickName) {
            var rows = {},defalutType = 'VARCHAR(255)',indexs = {};
            for (var key in models) {
                i ++;
                var data_type = models[key].data_type || ''
                // if (key == '_id' && !data_type) data_type = 'BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY';
                // if (key == 'id' && !data_type) data_type = 'BIGINT NOT NULL UNIQUE';
                // if (key == '_id' && !data_type) data_type = 'BIGINT NOT NULL AUTO_INCREMENT';
                if (key == 'id' && !data_type) data_type = 'BIGINT';
                models[key].rule = models[key].rule || {};
                if (!data_type) {
                    var datatype = models[key].rule.PositiveNum ? 'DOUBLE' : (models[key].rule.PositiveInt ? 'INT' : defalutType);
                    if(/sort|state|level|status/.test(key)
                    || models[key].type == 'switch' 
                    || (models[key].attr && models[key].attr.type == 'number')){
                    datatype = 'INT';
                    }else if(models[key].type == 'datetime'){
                    datatype = 'BIGINT';
                    }
                    data_type += datatype;
                    //if (models[key].rule.NotNull) data_type += ' NOT NULL';
                }
                if (/file|editor|table/.test(models[key].type)) {
                    data_type = 'LONGTEXT';
                }else if(/image/.test(models[key].type) && models[key].attr && models[key].attr.limit > 2){
                    data_type = 'LONGTEXT';
                }
                let keyDesc = models[key].desc || models[key].label
                if(models[key].attr && models[key].attr.actions){
                    let descs = []
                    for(let i = 0; i < models[key].attr.actions.length;i ++){
                        let item = models[key].attr.actions[i]
                        descs.push(`${item.name}:${item.value}`)
                    }
                    if(descs.length) keyDesc += ` ${descs.join(',')}`
                }
                // sheel0[i] = [
                //     'hw',
                //     name,
                //     desc,
                //     key,
                //     keyDesc,
                //     key == 'id' ? '是' : '否',
                //     data_type,0,'','','']
                rows[key] = key + ' ' + (data_type || defalutType);
                ///唯一
                if(models[key].unique) rows[key] += ' UNIQUE';
                ///默认值
                if(models[key].defaultValue){
                    if(/^[0-9]*$/.test(models[key].defaultValue)){
                    rows[key] += ' DEFAULT ' + models[key].defaultValue;
                    }else{
                    rows[key] += ' DEFAULT "' + models[key].defaultValue + '"';
                    }
                }
                ///描述
                if(models[key].label) rows[key] += ' COMMENT "' + models[key].label + '"';
                ///索引
                if (models[key].index) {
                    let indexName = typeof models[key].index == 'string' ? models[key].index : ('IDX_' + key)
                    if(!indexs[indexName]) indexs[indexName] = []
                    indexs[indexName].push(key)
                }
                //if (models[key].index) indexs.push(',INDEX ' + (typeof models[key].index == 'string' ? models[key].index : ('IDX_' + key)) + '(' + key + ')')
                let datatype1 = '字符串型C'
                if(/INT/i.test(data_type)){
                    datatype1 = '数值型N'
                }else if(/date/i.test(data_type)){
                    datatype1 = '时间日期型T'
                }else if(/LONGTEXT/i.test(data_type)){
                    datatype1 = '长文本text'
                }
                sheel0[i] = [
                    tableNickName,
                    '智慧市容环卫综合应用管理系统',
                    '数据库',
                    'mysql',
                    '市场监管',
                    '实时',
                    '实时',
                    '2级（受限数据）',
                    '',
                    models[key].label || key,
                    key,
                    '否',
                    '',
                    datatype1,
                    '否',
                    '',
                    /VARCHAR\(255\)/i.test(data_type) ? '255' : '',
                    key == 'id' ? '是' : '否',
                    (/(NOT NULL)|(PRIMARY)/i.test(data_type) || key == 'id') ? '否' : '是',
                    '有条件共享',
                    '根据应用场景受限共享',
                    '有条件开放',
                    '根据应用场景受限开放',
                    models[key].desc || '',
                    models[key].defaultValue
                ]
            }
        }
    }
    function getSchemas1(tables){///获取数据字典
        const workSheetsFromFile = xlsx.parse(`./数据字典导入模版.xlsx`);
        let sheel0 = workSheetsFromFile[0].data;
        let sheel1 = workSheetsFromFile[1].data;
        sheel0[0] = ['源系统库名','源系统表名','源系统表说明','字段','字段说明','是否主键','数据类型','空值率','最早数据日期','最晚数据日期','备注']
        sheel1[0] = ['序号','表名称','表描述','表记录数','表日均增量','是否接入','接入策略','增量时间字段','增量时间格式','备注']
        let i = 0,j = 1
        for(let key in (tables || models)){
            let desc = models[key].nickName || ''
            if(models[key].desc) desc += ',' + models[key].desc
            createTable(key,models[key].schemas,desc)
            sheel1[j] = [j,key,desc,0,0,'否','','createTime','时间戳(毫秒)','']
            j ++
        }
        let excelPath = path.resolve('./数据字典导出结果.xlsx')
        fs.writeFile(excelPath, xlsx.build(workSheetsFromFile), err => {
            //console.log(err)
        })
        function createTable(name,models,desc) {
            var rows = {},defalutType = 'VARCHAR(255)',indexs = {};
            for (var key in models) {
                i ++;
            var data_type = models[key].data_type || ''
            // if (key == '_id' && !data_type) data_type = 'BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY';
            // if (key == 'id' && !data_type) data_type = 'BIGINT NOT NULL UNIQUE';
            // if (key == '_id' && !data_type) data_type = 'BIGINT NOT NULL AUTO_INCREMENT';
            if (key == 'id' && !data_type) data_type = 'BIGINT';
            models[key].rule = models[key].rule || {};
            if (!data_type) {
                var datatype = models[key].rule.PositiveNum ? 'DOUBLE' : (models[key].rule.PositiveInt ? 'INT' : defalutType);
                if(/sort|state|level|status/.test(key)
                || models[key].type == 'switch' 
                || (models[key].attr && models[key].attr.type == 'number')){
                datatype = 'INT';
                }else if(models[key].type == 'datetime'){
                datatype = 'BIGINT';
                }
                data_type += datatype;
                //if (models[key].rule.NotNull) data_type += ' NOT NULL';
            }
            if (/file|editor|table/.test(models[key].type)) {
                data_type = 'LONGTEXT';
            }else if(/image/.test(models[key].type) && models[key].attr && models[key].attr.limit > 2){
                data_type = 'LONGTEXT';
            }
            let keyDesc = models[key].desc || models[key].label
            if(models[key].attr && models[key].attr.actions){
                let descs = []
                for(let i = 0; i < models[key].attr.actions.length;i ++){
                    let item = models[key].attr.actions[i]
                    descs.push(`${item.name}:${item.value}`)
                }
                if(descs.length) keyDesc += ` ${descs.join(',')}`
            }
            sheel0[i] = [
                'hw',
                name,
                desc,
                key,
                keyDesc,
                key == 'id' ? '是' : '否',
                data_type,0,'','','']
            rows[key] = key + ' ' + (data_type || defalutType);
            ///唯一
            if(models[key].unique) rows[key] += ' UNIQUE';
            ///默认值
            if(models[key].defaultValue){
                if(/^[0-9]*$/.test(models[key].defaultValue)){
                rows[key] += ' DEFAULT ' + models[key].defaultValue;
                }else{
                rows[key] += ' DEFAULT "' + models[key].defaultValue + '"';
                }
            }
            ///描述
            if(models[key].label) rows[key] += ' COMMENT "' + models[key].label + '"';
            ///索引
            if (models[key].index) {
                let indexName = typeof models[key].index == 'string' ? models[key].index : ('IDX_' + key)
                if(!indexs[indexName]) indexs[indexName] = []
                indexs[indexName].push(key)
            }
            //if (models[key].index) indexs.push(',INDEX ' + (typeof models[key].index == 'string' ? models[key].index : ('IDX_' + key)) + '(' + key + ')')
            sheel0[i] = [
                'hw',
                name,
                desc,
                key,
                keyDesc,
                key == 'id' ? '是' : '否',
                data_type,0,'','','']
            }
        }
    }
    
}